import pandas as pd
# voc=pd.read_pickle("total_voc_new.pkl")
# voc1=voc["voc1"].keys()
# voc=voc["voc"]
#
# voc_set=set()
# for i in set(voc1)-set(voc):
#     for j in list(i):
#         voc_set.update(j)
# print(len(set(voc)&voc_set)/len(voc_set))
# 由于交集特别少无法 通过短的拼接完成故而使用打星查表的方式解决问题
from glob import glob
#
# paths = glob("F:/sky_pile_thr/*")
# new_data=[]
# for i,path in enumerate(paths):
#     data = pd.read_pickle(path, compression="zip")
#     new_data+=data
#     if (i+1)%8==0:
#         pd.DataFrame(new_data).drop_duplicates([0,1,2]).to_pickle("F:/sky_pile_four/{}.pkl".format(i),compression="zip")
#         new_data = []
#         print(i)

# from glob import glob
#
# from tqdm import tqdm
#
# paths = glob("F:/sky_pile_four/*")
# new_data=[]
# for i,path in tqdm(enumerate(paths)):
#     data = pd.read_pickle(path, compression="zip")
#     new_data.append(data)
#     if (i+1)%3==0:
#         pd.concat(new_data).drop_duplicates([0,1,2]).to_pickle("F:/sky_pile_five/{}.pkl".format(i),compression="zip")
#         new_data = []
#         print(i)
# pd.concat(new_data).drop_duplicates([0,1,2]).to_pickle("F:/sky_pile_five/{}.pkl".format(i),compression="zip")
#


import sqlite3
import pandas as pd
from glob import glob
from tqdm import tqdm

sqlite3_db_path = "F:\\sky_pile_five\\data.db"  # SQLite3 数据库路径
paths = glob("F:/sky_pile_four/*.pkl")  # 获取所有 .pkl.zip 文件路径

# 建立与 SQLite 数据库的连接
conn = sqlite3.connect(sqlite3_db_path)

# 使用第一个 DataFrame 的结构创建表
if paths:
    # 读取第一个文件作为样本，确定表结构
    sample_df = pd.read_pickle(paths[0], compression="zip")
    sample_df.to_sql('my_table', conn, if_exists='replace', index=False)

# 开启事务
with conn:
    for i, path in tqdm(enumerate(paths), total=len(paths)):
        try:
            data = pd.read_pickle(path, compression="zip")
            data.to_sql('my_table', conn, if_exists='append', index=False)
        except Exception as e:
            print(f"Failed to process file {path}: {e}")

